﻿create PROCEDURE Sync.SP_CustomerLoans_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @CustomerLoans_Temp TABLE 
		(
			LoanID UniqueIdentifier,
			[LoanNumber] int,
				[CustomerID] uniqueidentifier,
				[ProjectStageID] uniqueidentifier,
				[CommitteeID] uniqueidentifier,
				[LoanName] nvarchar(50),
				[LoanPaymentMethodID] uniqueidentifier,
				[ResponsibleID] uniqueidentifier,
				[IsLoanProjectExist] bit,
				[LoanPlaceTypeID] uniqueidentifier,
				[LoanAmount] float,
				[LoanPaymentPeriod] int,
				[LegalFormID] uniqueidentifier,
				[LoanCreatedDate] datetime,
				[LoanTypeID] uniqueidentifier,
				[RelationWithManagers] nvarchar(MAX),
				[ReceiptID] uniqueidentifier,
				[FamilyCurrentDebts] nvarchar(MAX),
				[LoanAddress] nvarchar(MAX),
				[GuarantorID] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @CustomerLoans_Temp 
	   SELECT	Tbl.Col.value('LoanID[1]','uniqueidentifier') LoanID,
                Tbl.Col.value('LoanNumber[1]', 'int') [LoanNumber],
Tbl.Col.value('CustomerID[1]', 'uniqueidentifier') [CustomerID],
Tbl.Col.value('ProjectStageID[1]', 'uniqueidentifier') [ProjectStageID],
Tbl.Col.value('CommitteeID[1]', 'uniqueidentifier') [CommitteeID],
Tbl.Col.value('LoanName[1]', 'nvarchar(50)') [LoanName],
Tbl.Col.value('LoanPaymentMethodID[1]', 'uniqueidentifier') [LoanPaymentMethodID],
Tbl.Col.value('ResponsibleID[1]', 'uniqueidentifier') [ResponsibleID],
Tbl.Col.value('IsLoanProjectExist[1]', 'bit') [IsLoanProjectExist],
Tbl.Col.value('LoanPlaceTypeID[1]', 'uniqueidentifier') [LoanPlaceTypeID],
Tbl.Col.value('LoanAmount[1]', 'float') [LoanAmount],
Tbl.Col.value('LoanPaymentPeriod[1]', 'int') [LoanPaymentPeriod],
Tbl.Col.value('LegalFormID[1]', 'uniqueidentifier') [LegalFormID],
Tbl.Col.value('LoanCreatedDate[1]', 'datetime') [LoanCreatedDate],
Tbl.Col.value('LoanTypeID[1]', 'uniqueidentifier') [LoanTypeID],
Tbl.Col.value('RelationWithManagers[1]', 'nvarchar(MAX)') [RelationWithManagers],
Tbl.Col.value('ReceiptID[1]', 'uniqueidentifier') [ReceiptID],
Tbl.Col.value('FamilyCurrentDebts[1]', 'nvarchar(MAX)') [FamilyCurrentDebts],
Tbl.Col.value('LoanAddress[1]', 'nvarchar(MAX)') [LoanAddress],
Tbl.Col.value('GuarantorID[1]', 'uniqueidentifier') [GuarantorID],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/CustomerLoans') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[CustomerLoans] ([LoanID],[LoanNumber],[CustomerID],[ProjectStageID],[CommitteeID],[LoanName],[LoanPaymentMethodID],[ResponsibleID],[IsLoanProjectExist],[LoanPlaceTypeID],[LoanAmount],[LoanPaymentPeriod],[LegalFormID],[LoanCreatedDate],[LoanTypeID],[RelationWithManagers],[ReceiptID],[FamilyCurrentDebts],[LoanAddress],[GuarantorID])
	SELECT t.[LoanID],t.[LoanNumber],t.[CustomerID],t.[ProjectStageID],t.[CommitteeID],t.[LoanName],t.[LoanPaymentMethodID],t.[ResponsibleID],t.[IsLoanProjectExist],t.[LoanPlaceTypeID],t.[LoanAmount],t.[LoanPaymentPeriod],t.[LegalFormID],t.[LoanCreatedDate],t.[LoanTypeID],t.[RelationWithManagers],t.[ReceiptID],t.[FamilyCurrentDebts],t.[LoanAddress],t.[GuarantorID]
	FROM  @CustomerLoans_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.CustomerLoans_Tracking s
        WHERE t.LoanID = s.LoanID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[CustomerLoans]
	SET [CustomerLoans].[LoanNumber] = t.[LoanNumber],[CustomerLoans].[CustomerID] = t.[CustomerID],[CustomerLoans].[ProjectStageID] = t.[ProjectStageID],[CustomerLoans].[CommitteeID] = t.[CommitteeID],[CustomerLoans].[LoanName] = t.[LoanName],[CustomerLoans].[LoanPaymentMethodID] = t.[LoanPaymentMethodID],[CustomerLoans].[ResponsibleID] = t.[ResponsibleID],[CustomerLoans].[IsLoanProjectExist] = t.[IsLoanProjectExist],[CustomerLoans].[LoanPlaceTypeID] = t.[LoanPlaceTypeID],[CustomerLoans].[LoanAmount] = t.[LoanAmount],[CustomerLoans].[LoanPaymentPeriod] = t.[LoanPaymentPeriod],[CustomerLoans].[LegalFormID] = t.[LegalFormID],[CustomerLoans].[LoanCreatedDate] = t.[LoanCreatedDate],[CustomerLoans].[LoanTypeID] = t.[LoanTypeID],[CustomerLoans].[RelationWithManagers] = t.[RelationWithManagers],[CustomerLoans].[ReceiptID] = t.[ReceiptID],[CustomerLoans].[FamilyCurrentDebts] = t.[FamilyCurrentDebts],[CustomerLoans].[LoanAddress] = t.[LoanAddress],[CustomerLoans].[GuarantorID] = t.[GuarantorID] 
	FROM [dbo].[CustomerLoans] s JOIN @CustomerLoans_Temp t 
    ON t.LoanID = s.LoanID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.CustomerLoans_Tracking r
				  WHERE t.LoanID = r.LoanID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[CustomerLoans]
    FROM @CustomerLoans_Temp t JOIN [dbo].[CustomerLoans] s
    ON t.LoanID = s.LoanID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END














